package housing.task3.jdbc;

import housing.task3.dao.HsAddressDao;
import housing.task3.dao.HsRenterDao;
import housing.task3.dto.HsAddress;
import housing.task3.dto.PK.HsAddressPk;
import housing.task3.dto.HsRenter;
import housing.task3.dto.PK.HsRenterPk;
import housing.task3.exceptions.HsAddressDaoException;
import housing.task3.exceptions.HsRenterDaoException;
import housing.task3.factory.HsAddressDaoFactory;
import housing.task3.factory.HsRenterDaoFactory;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public class HsAddressDaoImpl extends AbstractDAO implements HsAddressDao {
    /**
     * The factory class for this DAO has two versions of the create() method - one that
     * takes no arguments and one that takes a Connection argument. If the Connection version
     * is chosen then the connection will be stored in this attribute and will be used by all
     * calls to this DAO, otherwise a new Connection will be allocated for each operation.
     */
    protected Connection userConn;

    protected static final Logger logger = Logger.getLogger(HsAddressDaoImpl.class);

    /**
     * All finder methods in this class use this SELECT constant to build their queries
     */
    protected final String SQL_SELECT = "SELECT address_id, street, house, flat FROM " + getTableName() + "";

    /**
     * Finder methods will pass this value to the JDBC setMaxRows method
     */
    protected int maxRows;

    /**
     * SQL INSERT statement for this table
     */
    protected final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( address_id, street, house, flat ) VALUES ( ?, ?, ?, ? )";

    /**
     * SQL UPDATE statement for this table
     */
    protected final String SQL_UPDATE = "UPDATE " + getTableName() + " SET address_id = ?, street = ?, house = ?, flat = ? WHERE address_id = ?";

    /**
     * SQL DELETE statement for this table
     */
    protected final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE address_id = ?";

    /**
     * SQL DELETE [*] statement for this table
     */
    protected final String SQL_DELETE_ALL = "DELETE FROM " + getTableName();

    /**
     * Index of column address_id
     */
    protected static final int COLUMN_ADDRESS_ID = 1;

    /**
     * Index of column street
     */
    protected static final int COLUMN_STREET = 2;

    /**
     * Index of column house
     */
    protected static final int COLUMN_HOUSE = 3;

    /**
     * Index of column flat
     */
    protected static final int COLUMN_FLAT = 4;

    /**
     * Number of columns
     */
    protected static final int NUMBER_OF_COLUMNS = 4;

    /**
     * Index of primary-key column address_id
     */
    protected static final int PK_COLUMN_ADDRESS_ID = 1;

    /**
     * Inserts a new row in the HS_Address table.
     */
    public HsAddressPk insert(HsAddress dto) throws HsAddressDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            stmt.setInt(index++, dto.getAddressId());
            stmt.setString(index++, dto.getStreet());
            stmt.setInt(index++, dto.getHouse());
            if (dto.isFlatNull()) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getFlat());
            }

            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_INSERT + " with DTO: " + dto);
            }

            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

            reset(dto);
            return dto.createPk();
        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsAddressDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Updates a single row in the HS_Address table.
     */
    public void update(HsAddressPk pk, HsAddress dto) throws HsAddressDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_UPDATE + " with DTO: " + dto);
            }

            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            stmt.setInt(index++, dto.getAddressId());
            stmt.setString(index++, dto.getStreet());
            stmt.setInt(index++, dto.getHouse());
            if (dto.isFlatNull()) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getFlat());
            }

            stmt.setInt(5, pk.getAddressId());
            int rows = stmt.executeUpdate();
            reset(dto);
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsAddressDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Deletes a single row in the HS_Address table.
     */
    public void delete(HsAddressPk pk) throws HsAddressDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_DELETE + " with PK: " + pk);
            }

            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, pk.getAddressId());
            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsAddressDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Deletes all records from the table
     * and records from other tables by Foreign Key
     */
    public void deleteAllRecords() throws HsAddressDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            //=========================================================
            //Delete from connected table by Foreign key
            deleteForeigns(conn);
            //=========================================================

            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_DELETE_ALL + " with table: HS_Address");
            }

            stmt = conn.prepareStatement(SQL_DELETE_ALL);
            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsAddressDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }
    }

    /**
     * Returns the rows from the HS_Address table that matches the specified primary-key value.
     */
    public HsAddress findByPrimaryKey(HsAddressPk pk) throws HsAddressDaoException {
        return findByPrimaryKey(pk.getAddressId());
    }

    /**
     * Returns all rows from the HS_Address table that match the criteria 'address_id = :addressId'.
     */
    public HsAddress findByPrimaryKey(int addressId) throws HsAddressDaoException {
        HsAddress ret[] = findByDynamicSelect(SQL_SELECT + " WHERE address_id = ?", new Object[]{new Integer(addressId)});
        return ret.length == 0 ? null : ret[0];
    }

    /**
     * Returns all rows from the HS_Address table that match the criteria ''.
     */
    public HsAddress[] findAll() throws HsAddressDaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY address_id", null);
    }

    /**
     * Returns all rows from the HS_Address table that match the criteria 'address_id = :addressId'.
     */
    public HsAddress[] findWhereAddressIdEquals(int addressId) throws HsAddressDaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE address_id = ? ORDER BY address_id", new Object[]{new Integer(addressId)});
    }

    /**
     * Returns all rows from the HS_Address table that match the criteria 'street = :street'.
     */
    public HsAddress[] findWhereStreetEquals(String street) throws HsAddressDaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE street = ? ORDER BY street", new Object[]{street});
    }

    /**
     * Returns all rows from the HS_Address table that match the criteria 'house = :house'.
     */
    public HsAddress[] findWhereHouseEquals(int house) throws HsAddressDaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE house = ? ORDER BY house", new Object[]{new Integer(house)});
    }

    /**
     * Returns all rows from the HS_Address table that match the criteria 'flat = :flat'.
     */
    public HsAddress[] findWhereFlatEquals(int flat) throws HsAddressDaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE flat = ? ORDER BY flat", new Object[]{new Integer(flat)});
    }

    /**
     * Method 'HsAddressDaoImpl'
     */
    public HsAddressDaoImpl() {
    }

    /**
     * Method 'HsAddressDaoImpl'
     *
     * @param userConn
     */
    public HsAddressDaoImpl(final java.sql.Connection userConn) {
        this.userConn = userConn;
    }

    /**
     * Sets the value of maxRows
     */
    public void setMaxRows(int maxRows) {
        this.maxRows = maxRows;
    }

    /**
     * Gets the value of maxRows
     */
    public int getMaxRows() {
        return maxRows;
    }

    /**
     * Method 'getTableName'
     *
     * @return String
     */
    public String getTableName() {
        return "HS_Address";
    }

    /**
     * Fetches a single row from the result set
     */
    protected HsAddress fetchSingleResult(ResultSet rs) throws SQLException {
        if (rs.next()) {
            HsAddress dto = new HsAddress();
            populateDto(dto, rs);
            return dto;
        } else {
            return null;
        }

    }

    /**
     * Fetches multiple rows from the result set
     */
    protected HsAddress[] fetchMultiResults(ResultSet rs) throws SQLException {
        Collection resultList = new ArrayList();
        while (rs.next()) {
            HsAddress dto = new HsAddress();
            populateDto(dto, rs);
            resultList.add(dto);
        }

        HsAddress ret[] = new HsAddress[resultList.size()];
        resultList.toArray(ret);
        return ret;
    }

    /**
     * Populates a DTO with data from a ResultSet
     */
    protected void populateDto(HsAddress dto, ResultSet rs) throws SQLException {
        dto.setAddressId(rs.getInt(COLUMN_ADDRESS_ID));
        dto.setStreet(rs.getString(COLUMN_STREET));
        dto.setHouse(rs.getInt(COLUMN_HOUSE));
        dto.setFlat(rs.getInt(COLUMN_FLAT));
        if (rs.wasNull()) {
            dto.setFlatNull(true);
        }

    }

    /**
     * Resets the modified attributes in the DTO
     */
    protected void reset(HsAddress dto) {
    }

    /**
     * Returns all rows from the HS_Address table that match the specified arbitrary SQL statement
     */
    public HsAddress[] findByDynamicSelect(String sql, Object[] sqlParams) throws HsAddressDaoException {
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            // construct the SQL statement
            final String SQL = sql;


            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL);
            }

            // prepare statement
            stmt = conn.prepareStatement(SQL);
            stmt.setMaxRows(maxRows);

            // bind parameters
            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }


            rs = stmt.executeQuery();

            // fetch the results
            return fetchMultiResults(rs);
        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsAddressDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(rs);
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Returns all rows from the HS_Address table that match the specified arbitrary SQL statement
     */
    public HsAddress[] findByDynamicWhere(String sql, Object[] sqlParams) throws HsAddressDaoException {
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            // construct the SQL statement
            final String SQL = SQL_SELECT + " WHERE " + sql;


            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL);
            }

            // prepare statement
            stmt = conn.prepareStatement(SQL);
            stmt.setMaxRows(maxRows);

            // bind parameters
            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }


            rs = stmt.executeQuery();

            // fetch the results
            return fetchMultiResults(rs);
        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsAddressDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(rs);
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    private void deleteForeigns(Connection conn) throws HsAddressDaoException, HsRenterDaoException {
        HsRenterDao renterDao =
                HsRenterDaoFactory.create(conn);
        HsAddressDao addressDao =
                HsAddressDaoFactory.create(conn);
        HsAddress[] toBeDeleted =
                addressDao.findAll();

        for (HsAddress addrDto : toBeDeleted) {
            HsRenter[] renterDto =
                    renterDao.findWhereAddressIdEquals(addrDto.getAddressId());

            for (HsRenter renter : renterDto) {
                HsRenterPk pk =
                        new HsRenterPk(renter.getRenterId());
                renterDao.delete(pk);
            }
        }
    }

}
